﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using GeneratorLib.DB;
using GeneratorLib.DB.Connectios;
using GeneratorLib.DB.Schemas;

namespace Generator.Generators
{
    public partial class ServerController_SQLServer : GeneratorCodeBase
    {

        public ServerController_SQLServer()
        {
            InitializeComponent();
            this.GeneratorName = "SQL Server ServerController Generator";
            AddToolItems();
        }



        private void AddToolItems()
        {

            ToolStripMenuItem item0 = new ToolStripMenuItem("List Select");
            item0.Tag = 0;
            ToolStripMenuItem item1 = new ToolStripMenuItem("List Update");
            item1.Tag = 1;

            ToolStripSeparator items01 = new ToolStripSeparator();

            ToolStripMenuItem item20 = new ToolStripMenuItem("Edit Select");
            item20.Tag = 20;
            ToolStripMenuItem item21 = new ToolStripMenuItem("Edit Insert");
            item21.Tag = 21;
            ToolStripMenuItem item22 = new ToolStripMenuItem("Edit Update");
            item22.Tag = 22;

            ToolStripSeparator items02 = new ToolStripSeparator();

            ToolStripMenuItem item30 = new ToolStripMenuItem("ListItem Select");
            item30.Tag = 30;


            this.AddToolStripItem(item0);
            this.AddToolStripItem(item1);
            this.AddToolStripItem(items01);
            this.AddToolStripItem(item20);
            this.AddToolStripItem(item21);
            this.AddToolStripItem(item22);
            this.AddToolStripItem(items02);
            this.AddToolStripItem(item30);

        }

        public override void Generate()
        {
            base.Generate();
        }

        public override void Generate(ToolStripItem item)
        {
            base.Generate(item);

            //this.Table = MYSQLUtility.GetTableInfo(this.Connection, this.Table.TableName);
            txtCode.ResetText();
            CodeBuilder.Clear();
            switch (Convert.ToInt32(item.Tag))
            {

                case 0:
                    createListQueryClass();
                    break;

                case 1:
                    createListUpdateClass();
                    break;

                case 20:
                    createQueryClass();
                    break;
                case 21:
                    createInsertClass();
                    break;
                case 22:
                    createUpdateClass();
                    break;
                case 30:
                    createListSelectQueryClass();
                    break;
                default:
                    break;
            }

            txtCode.Text = CodeBuilder.ToString();
            txtCode.Refresh();
        }


        private void createListQueryClass()
        {
            TableInfoAddin tableAddin = this.Connection.FindTableAddin(this.Table.TableName);

            var res = from s in this.Connection.Relations
                      where
                          s.ForeignTable.TableName.ToLower().CompareTo(this.Table.TableName.ToLower()) == 0
                      select s;


            AppendUsing();
            AppendNameSpaceStart();
            AppandClassStart("ListSelect", "ServerControlBase", string.Format("{0}データを取得", tableAddin.TableDisplayName));
            AppendMethodStart();

            FieldInfo keyField = this.Table.GetKeyField();
            //string simpleTableName = Table.IsMasterTable ? "m" : "t";

            int fieldIndex = 0;
            List<FieldInfo> searchFields = new List<FieldInfo>();
            List<FieldInfo> selectFields = new List<FieldInfo>();
            List<Relation> innerJoinRelations = new List<Relation>();

            fieldIndex = 0;
            foreach (FieldInfo field in Table.Fields)
            {
                fieldIndex++;
                if (tableAddin.IsListField(field.FieldName))
                {
                    selectFields.Add(field);
                }

                if (tableAddin.IsSearchField(field.FieldName))
                {
                    searchFields.Add(field);
                }
            }

            fieldIndex = 0;
            foreach (var r in res)
            {
                fieldIndex++;
                if (!tableAddin.IsListField(r.ForeignTable.KeyFieldName))
                    continue;

                TableInfo tTable = this.Connection.FindTable(r.PrimaryTable.TableName);
                FieldInfo tField = tTable.FindField(r.PrimaryTable.ValueFieldName);
                selectFields.Add(tField);
            }

            fieldIndex = 0;
            foreach (var r in res)
            {
                fieldIndex++;
                if (!tableAddin.IsListField(r.ForeignTable.KeyFieldName))
                    continue;
                
                innerJoinRelations.Add(r);
            }

            CodeBuilder.AppendLine("            // 検索条件");
            foreach (FieldInfo field in searchFields)
            {
                CodeBuilder.AppendLine("            {0}", getSingleQueryConditionForSearch(field));
            }

            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("            List<SqlParameter> paramList = new List<SqlParameter>();");

            fieldIndex = 0;
            foreach (FieldInfo field in searchFields)
            {
                fieldIndex++;
                if (field.GetCSharpTypeNullable())
                {
                    CodeBuilder.AppendLine("            if ({0}.HasValue)", field.FieldName);
                    CodeBuilder.AppendLine("                paramList.Add(new SqlParameter(\"@{0}\", SqlDbType.{1}) {{ Value = {0}.Value }});", field.FieldName, field.GetFieldType());
                }
                else
                {
                    CodeBuilder.AppendLine("            paramList.Add(new SqlParameter(\"@{0}\", SqlDbType.{1}){{ Value = {0} }});", field.FieldName, field.GetFieldType());
                }
            }


            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("            SqlParameter[] param = paramList.ToArray();");
            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("            SqlBuilder Sql = new SqlBuilder();");
            CodeBuilder.AppendLine("            Sql.AppendFormat(\" SELECT \");");

            fieldIndex = 0;
            foreach (FieldInfo field in selectFields)
            {
                fieldIndex++;
                if (fieldIndex < selectFields.Count)
                {
                    CodeBuilder.AppendLine("            Sql.AppendFormat(\"     {0}.{1}, \");", field.Table.TableName, field.FieldName);
                }
                else
                {
                    CodeBuilder.AppendLine("            Sql.AppendFormat(\"     {0}.{1}  \");", field.Table.TableName, field.FieldName);
                }

            }
            CodeBuilder.AppendLine("            Sql.AppendFormat(\" FROM \");");
            CodeBuilder.AppendLine("            Sql.AppendFormat(\"     {0}  \");", Table.TableName);

            foreach (var r in innerJoinRelations)
            {
                CodeBuilder.AppendLine("            Sql.AppendFormat(\" INNER JOIN \");");
                CodeBuilder.AppendLine("            Sql.AppendFormat(\"    {0}\");", r.PrimaryTable.TableName);
                CodeBuilder.AppendLine("            Sql.AppendFormat(\" ON \");");
                CodeBuilder.AppendLine("            Sql.AppendFormat(\"    {0}.{1}={2}.{3}\");",
                    r.PrimaryTable.TableName, r.PrimaryTable.KeyFieldName, r.ForeignTable.TableName, r.ForeignTable.KeyFieldName);
            }


            CodeBuilder.AppendLine("            Sql.AppendFormat(\" WHERE \");");
            CodeBuilder.AppendLine("            Sql.AppendFormat(\"         {0}.StatusFlag = 0 \");", Table.TableName);
            if (this.Table.IsMasterTable)
                CodeBuilder.AppendLine("            Sql.AppendFormat(\" AND     {0}.DisplayFlag = 1 \");", Table.TableName);

            foreach (var r in innerJoinRelations)
            {
                CodeBuilder.AppendLine("            Sql.AppendFormat(\" AND     {0}.StatusFlag = 0 \");", r.PrimaryTable.TableName);
                CodeBuilder.AppendLine("            Sql.AppendFormat(\" AND     {0}.DisplayFlag = 1 \");", r.PrimaryTable.TableName);
            }


            foreach (FieldInfo field in searchFields)
            {
                if (field.GetCSharpTypeNullable())
                {
                    CodeBuilder.AppendLine("            if ({0}.HasValue)", field.FieldName);
                    CodeBuilder.AppendLine("                Sql.AppendFormat(\" AND     {1}.{0} = @{0} \");", field.FieldName, Table.TableName);
                }
                else
                {
                    CodeBuilder.AppendLine("            Sql.AppendFormat(\" AND     {1}.{0} = @{0} \");", field.FieldName, Table.TableName);
                }
            }
            if (this.Table.IsMasterTable)
            {
                CodeBuilder.AppendLine("            Sql.AppendFormat(\" ORDER BY \");");
                CodeBuilder.AppendLine("            Sql.AppendFormat(\"     {0}.DisplayNo, {0}.{1} \");", Table.TableName, this.Table.GetKeyField().FieldName);
            }

            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("            List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();");
            CodeBuilder.AppendLine("            this.ExecuteSelectQuery(Sql.CommandText, null, out list, param);");
            CodeBuilder.AppendLine("            this.OutParam.ListParam = list;");
            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("            return true;");

            AppendMethodEnd();
            AppandClassEnd();
            AppendNameSpaceEnd();

        }

        private void createListUpdateClass()
        {
            TableInfoAddin tableAddin = this.Connection.FindTableAddin(this.Table.TableName);
            AppendUsing();
            AppendNameSpaceStart();
            AppandClassStart("ListUpdate", "ServerControlBase", string.Format("{0}データを更新", tableAddin.TableDisplayName));
            AppendMethodStart();

            int fieldIndex = 0;

            List<FieldInfo> listFields = new List<FieldInfo>();

            foreach (FieldInfo field in Table.Fields)
            {
                fieldIndex++;
                if (tableAddin.IsListField(field.FieldName))
                {
                    listFields.Add(field);
                }
            }

            FieldInfo keyField = this.Table.GetKeyField();
            if (!listFields.Contains(keyField))
            {
                listFields.Insert(0, keyField);
            }

            CodeBuilder.AppendLine("            // パラメタを取得");
            CodeBuilder.AppendLine("            List<Dictionary<string, object>> items = this.InParam.ListParam;");
            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("            foreach (Dictionary<string, object> item in items)");
            CodeBuilder.AppendLine("            {{");
            CodeBuilder.AppendLine("                bool IsUpdate = (bool)item[\"UpdateFlag\"];");
            CodeBuilder.AppendLine("                if (!IsUpdate)");
            CodeBuilder.AppendLine("                {{");
            CodeBuilder.AppendLine("                    continue;");
            CodeBuilder.AppendLine("                }}");
            CodeBuilder.AppendLine("");
            fieldIndex = 0;



            foreach (FieldInfo field in listFields)
            {
                fieldIndex++;
                CodeBuilder.AppendLine("                {0}", getSingleQueryCondition("item", field));
            }

            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("                // 検索条件");
            CodeBuilder.AppendLine("                SqlParameter[] param = new SqlParameter[]");
            CodeBuilder.AppendLine("                {{");
            fieldIndex = 0;
            foreach (FieldInfo field in listFields)
            {
                fieldIndex++;
                if (fieldIndex < listFields.Count)
                    CodeBuilder.AppendLine("                    new SqlParameter(\"@{0}\", SqlDbType.{1}){{ Value = {0} }},", field.FieldName, field.GetFieldType());
                else
                    CodeBuilder.AppendLine("                    new SqlParameter(\"@{0}\", SqlDbType.{1}){{ Value = {0} }}", field.FieldName, field.GetFieldType());
            }
            CodeBuilder.AppendLine("                }};");
            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("                SqlBuilder Sql = new SqlBuilder();");
            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("                Sql.Append(\" UPDATE {0} \");", Table.TableName);
            CodeBuilder.AppendLine("                Sql.Append(\" SET \");");
            fieldIndex = 0;
            foreach (FieldInfo field in listFields)
            {
                fieldIndex++;
                if (field != keyField)
                {
                    if (fieldIndex < listFields.Count)
                        CodeBuilder.AppendLine("                Sql.Append(\"         {0} = @{0}, \");", field.FieldName);
                    else
                        CodeBuilder.AppendLine("                Sql.Append(\"         {0} = @{0}  \");", field.FieldName);
                }

            }

            CodeBuilder.AppendLine("                Sql.Append(\" WHERE \");");
            CodeBuilder.AppendLine("                Sql.Append(\"         {0} = @{0} \");", keyField.FieldName);
            CodeBuilder.AppendLine("                Sql.Append(\" ; \");");
            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("                this.ExecuteNonQuery(Sql.CommandText, null, param);");
            CodeBuilder.AppendLine("            }}");
            CodeBuilder.AppendLine("            this.IsTransactionCompleted = true;");
            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("            return true;");

            AppendMethodEnd();
            AppandClassEnd();
            AppendNameSpaceEnd();
        }


        private void createQueryClass()
        {
            TableInfoAddin tableAddin = this.Connection.FindTableAddin(this.Table.TableName);

            AppendUsing();
            AppendNameSpaceStart();
            AppandClassStart("ItemSelect", "ServerControlBase", string.Format("{0}データを取得", tableAddin.TableDisplayName));
            AppendMethodStart();

            FieldInfo keyField = this.Table.GetKeyField();
            string simpleTableName = Table.IsMasterTable ? "m" : "t";



            CodeBuilder.AppendLine("            // 検索条件");
            CodeBuilder.AppendLine("            {0}", getSingleQueryCondition(keyField));
            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("            SqlBuilder Sql = new SqlBuilder();");
            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("            SqlParameter[] param = ");
            CodeBuilder.AppendLine("            {");
            CodeBuilder.AppendLine("                new SqlParameter(\"@{0}\", SqlDbType.{1}){{ Value = {0} }}", keyField.FieldName, keyField.GetFieldType());
            CodeBuilder.AppendLine("            };");
            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("            Sql.AppendFormat(\" SELECT \");");
            int fieldIndex = 0;
            foreach (FieldInfo field in Table.Fields)
            {
                fieldIndex++;
                if (fieldIndex < Table.Fields.Count)
                    CodeBuilder.AppendLine("            Sql.AppendFormat(\"     {0}.{1}, \");", simpleTableName, field.FieldName);
                else
                    CodeBuilder.AppendLine("            Sql.AppendFormat(\"     {0}.{1}  \");", simpleTableName, field.FieldName);
            }
            CodeBuilder.AppendLine("            Sql.AppendFormat(\" FROM \");");
            CodeBuilder.AppendLine("            Sql.AppendFormat(\"     {0} AS {1} \");", Table.TableName, simpleTableName);
            CodeBuilder.AppendLine("            Sql.AppendFormat(\" WHERE \");");
            CodeBuilder.AppendLine("            Sql.AppendFormat(\"     {1}.{0} = @{0} \");", keyField.FieldName, simpleTableName);
            if (Table.IsMasterTable)
                CodeBuilder.AppendLine("            Sql.AppendFormat(\" AND     {0}.StatusFlag = 0 \");", simpleTableName);

            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("            List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();");
            CodeBuilder.AppendLine("            this.ExecuteSelectQuery(Sql.CommandText, null, out list, param);");
            CodeBuilder.AppendLine("            this.OutParam.ListParam = list;");
            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("            return true;");

            AppendMethodEnd();
            AppandClassEnd();
            AppendNameSpaceEnd();

        }

        private void createInsertClass()
        {
            TableInfoAddin tableAddin = this.Connection.FindTableAddin(this.Table.TableName);
            AppendUsing();
            AppendNameSpaceStart();
            AppandClassStart("ItemInsert", "ServerControlBase", string.Format("{0}データを挿入", tableAddin.TableDisplayName));
            AppendMethodStart();

            int fieldIndex = 0;

            CodeBuilder.AppendLine("            // データを取得");

            FieldInfo keyField = this.Table.GetKeyField();


            fieldIndex = 0;
            foreach (FieldInfo field in Table.Fields)
            {
                fieldIndex++;
                if (field != keyField)
                    CodeBuilder.AppendLine("            {0}", getSingleQueryConditionForSearch(field));
            }


            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("            List<SqlParameter> paramList = new List<SqlParameter>();");

            fieldIndex = 0;
            foreach (FieldInfo field in Table.Fields)
            {
                fieldIndex++;
                if (field == keyField)
                    continue;

                if (field.GetCSharpTypeNullable())
                {
                    CodeBuilder.AppendLine("            if ({0}.HasValue)", field.FieldName);
                    CodeBuilder.AppendLine("                paramList.Add(new SqlParameter(\"@{0}\", SqlDbType.{1}) {{ Value = {0}.Value }});", field.FieldName, field.GetFieldType());
                }
                else
                {
                    CodeBuilder.AppendLine("            paramList.Add(new SqlParameter(\"@{0}\", SqlDbType.{1}){{ Value = {0} }});", field.FieldName, field.GetFieldType());
                }
            }


            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("            SqlParameter[] param = paramList.ToArray();");
            CodeBuilder.AppendLine("");


            #region "comment"
            /*
            codeBuilder.AppendLine("");
            codeBuilder.AppendLine("            SqlBuilder Sql = new SqlBuilder();");
            codeBuilder.AppendLine("");
            codeBuilder.AppendLine("            SqlParameter[] param = ");
            codeBuilder.AppendLine("            {");
            fieldIndex = 0;
            foreach (Field field in Table.Fields)
            {
                fieldIndex++;
                if (field != keyField)
                {
                    if (fieldIndex < Table.Fields.Count)
                        codeBuilder.AppendLine("                new SqlParameter(\"@{0}\", SqlDbType.{1}){{ Value = {0} }},", field.FieldName, field.GetFieldType());
                    else
                        codeBuilder.AppendLine("                new SqlParameter(\"@{0}\", SqlDbType.{1}){{ Value = {0} }}", field.FieldName, field.GetFieldType());
                }
            }
            codeBuilder.AppendLine("            };");
            codeBuilder.AppendLine("");
            */
            #endregion

            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("            SqlBuilder Sql = new SqlBuilder();");
            CodeBuilder.AppendLine("            #region \"SQL\"");
            if (this.Table.IsMasterTable)
            {
                CodeBuilder.AppendLine("            Sql.AppendFormat(\" declare @maxid int;\"");
                CodeBuilder.AppendLine("            Sql.AppendFormat(\" SELECT @maxid= isnull( max({0}),0) +1 FROM {1}; \");", this.Table.GetKeyField().FieldName, Table.TableName);
            }
            CodeBuilder.AppendLine("            Sql.AppendFormat(\" INSERT INTO {0} \");", Table.TableName);
            CodeBuilder.AppendLine("            Sql.AppendFormat(\"     ( \");");

            if (this.Table.IsMasterTable)
                CodeBuilder.AppendLine("            Sql.AppendFormat(\"     {0}\");", keyField.FieldName);

            fieldIndex = 0;
            foreach (FieldInfo field in Table.Fields)
            {
                fieldIndex++;
                if (field == keyField)
                    continue;

                if (field.GetCSharpTypeNullable())
                {
                    CodeBuilder.AppendLine("            if ({0}.HasValue)", field.FieldName);
                    CodeBuilder.AppendLine("                Sql.AppendFormat(\"     ,{0}\");", field.FieldName);
                }
                else
                {
                    CodeBuilder.AppendLine("            Sql.AppendFormat(\"     ,{0}\");", field.FieldName);
                }
            }

            CodeBuilder.AppendLine("            Sql.AppendFormat(\"    ) \");");
            CodeBuilder.AppendLine("            Sql.AppendFormat(\" VALUES \");");
            CodeBuilder.AppendLine("            Sql.AppendFormat(\" ( \");");
            fieldIndex = 0;
            if (this.Table.IsMasterTable)
                CodeBuilder.AppendLine("            Sql.AppendFormat(\"     {0}\");", "@maxid");
            foreach (FieldInfo field in Table.Fields)
            {
                fieldIndex++;
                if (field == keyField)
                    continue;

                if (field.GetCSharpTypeNullable())
                {
                    CodeBuilder.AppendLine("            if ({0}.HasValue)", field.FieldName);
                    CodeBuilder.AppendLine("                Sql.AppendFormat(\"     ,@{0}\");", field.FieldName);
                }
                else
                {
                    CodeBuilder.AppendLine("            Sql.AppendFormat(\"     ,@{0}\");", field.FieldName);
                }


            }

            CodeBuilder.AppendLine("            Sql.AppendFormat(\" ); \");");
            if (this.Table.IsMasterTable)
                CodeBuilder.AppendLine("            Sql.AppendFormat(\" SELECT  @maxid as LastInsertID; \");");
            else
                CodeBuilder.AppendLine("            Sql.AppendFormat(\" SELECT last_insert_id() as LastInsertID; \");");

            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("            #endregion");

            CodeBuilder.AppendLine("            StringBuilder sb= new StringBuilder(Sql.CommandText);");
            CodeBuilder.AppendLine("            System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@\"(?<=\\(\\s*),\");");
            CodeBuilder.AppendLine("            System.Text.RegularExpressions.MatchCollection matchs= regex.Matches(sb.ToString());");
            CodeBuilder.AppendLine("            foreach (System.Text.RegularExpressions.Match match in matchs)");
            CodeBuilder.AppendLine("            {{");
            CodeBuilder.AppendLine("                sb.Replace(\",\", \" \", match.Index, 1);");
            CodeBuilder.AppendLine("            }}");
            CodeBuilder.AppendLine("");

            CodeBuilder.AppendLine("            List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();");
            CodeBuilder.AppendLine("            this.ExecuteSelectQuery(sb.ToString(), null, out list, param);");
            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("            int LastInsertID = Converts.ToTryInt(list[0][\"LastInsertID\"], 0);");

            CodeBuilder.AppendLine("            //this.ExecuteNonQuery(sb.ToString(), null, param);");
            CodeBuilder.AppendLine("            //this.IsTransactionCompleted = true;");
            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("            return true;");

            AppendMethodEnd();
            AppandClassEnd();
            AppendNameSpaceEnd();
        }

        private void createUpdateClass()
        {
            TableInfoAddin tableAddin = this.Connection.FindTableAddin(this.Table.TableName);
            AppendUsing();
            AppendNameSpaceStart();
            AppandClassStart("ItemUpdate", "ServerControlBase", string.Format("{0}データを更新", tableAddin.TableDisplayName));
            AppendMethodStart();

            int fieldIndex = 0;
            FieldInfo keyField = this.Table.GetKeyField();

            CodeBuilder.AppendLine("            // データを取得");
            fieldIndex = 0;
            foreach (FieldInfo field in Table.Fields)
            {
                fieldIndex++;
                CodeBuilder.AppendLine("            {0}", getSingleQueryConditionForSearch(field));
            }

            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("            List<SqlParameter> paramList = new List<SqlParameter>();");

            fieldIndex = 0;
            foreach (FieldInfo field in Table.Fields)
            {
                fieldIndex++;
                if (field.GetCSharpTypeNullable())
                {
                    CodeBuilder.AppendLine("            if ({0}.HasValue)", field.FieldName);
                    CodeBuilder.AppendLine("                paramList.Add(new SqlParameter(\"@{0}\", SqlDbType.{1}) {{ Value = {0}.Value }});", field.FieldName, field.GetFieldType());
                }
                else
                {
                    CodeBuilder.AppendLine("            paramList.Add(new SqlParameter(\"@{0}\", SqlDbType.{1}){{ Value = {0} }});", field.FieldName, field.GetFieldType());
                }
            }


            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("            SqlParameter[] param = paramList.ToArray();");
            CodeBuilder.AppendLine("");

            /*
            codeBuilder.AppendLine("");
            codeBuilder.AppendLine("            SqlBuilder Sql = new SqlBuilder();");
            codeBuilder.AppendLine("");
            codeBuilder.AppendLine("            SqlParameter[] param = ");
            codeBuilder.AppendLine("            {");
            fieldIndex = 0;
            foreach (Field field in Table.Fields)
            {
                fieldIndex++;
                if (fieldIndex < Table.Fields.Count)
                    codeBuilder.AppendLine("                new SqlParameter(\"@{0}\", SqlDbType.{1}){{ Value = {0} }},", field.FieldName, field.GetFieldType());
                else
                    codeBuilder.AppendLine("                new SqlParameter(\"@{0}\", SqlDbType.{1}){{ Value = {0} }}", field.FieldName, field.GetFieldType());
            }
            codeBuilder.AppendLine("            };");
            codeBuilder.AppendLine("");

            */
            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("            SqlBuilder Sql = new SqlBuilder();");
            CodeBuilder.AppendLine("            #region \"SQL\"");

            CodeBuilder.AppendLine("                Sql.Append(\" UPDATE {0} \");", Table.TableName);
            CodeBuilder.AppendLine("                Sql.Append(\" SET \");");

            fieldIndex = 0;
            foreach (FieldInfo field in Table.Fields)
            {
                fieldIndex++;
                if (field == keyField)
                    continue;

                if (field.GetCSharpTypeNullable())
                {
                    CodeBuilder.AppendLine("            if ({0}.HasValue)", field.FieldName);
                    CodeBuilder.AppendLine("                Sql.Append(\"         ,{0} = @{0} \");", field.FieldName);
                }
                else
                {
                    CodeBuilder.AppendLine("                Sql.Append(\"         ,{0} = @{0} \");", field.FieldName);
                }

            }

            CodeBuilder.AppendLine("                Sql.Append(\" WHERE \");");
            CodeBuilder.AppendLine("                Sql.Append(\"         {0} = @{0} \");", keyField.FieldName);
            CodeBuilder.AppendLine("                Sql.Append(\" ; \");");

            CodeBuilder.AppendLine("            #endregion");

            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("            StringBuilder sb = new StringBuilder(Sql.CommandText);");
            CodeBuilder.AppendLine("            System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@\"(?<=set\\s*),\", System.Text.RegularExpressions.RegexOptions.IgnoreCase);");
            CodeBuilder.AppendLine("            System.Text.RegularExpressions.MatchCollection matchs = regex.Matches(sb.ToString());");
            CodeBuilder.AppendLine("            foreach (System.Text.RegularExpressions.Match match in matchs)");
            CodeBuilder.AppendLine("            {{");
            CodeBuilder.AppendLine("                sb.Replace(\",\", \" \", match.Index, 1);");
            CodeBuilder.AppendLine("            }}");
            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("            this.ExecuteNonQuery(sb.ToString(), null, param);");
            CodeBuilder.AppendLine("            this.IsTransactionCompleted = true;");
            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("            return true;");

            AppendMethodEnd();
            AppandClassEnd();
            AppendNameSpaceEnd();
        }

        private void createListSelectQueryClass()
        {
            TableInfoAddin tableAddin = this.Connection.FindTableAddin(this.Table.TableName);

            AppendUsing();
            AppendNameSpaceStart();
            AppandClassStart("ItemValueText", "ServerControlBase", string.Format("{0}データを取得", tableAddin.TableDisplayName));
            AppendMethodStart();

            FieldInfo keyField = this.Table.GetKeyField();
            string simpleTableName = Table.IsMasterTable ? "m" : "t";



            CodeBuilder.AppendLine("            // 検索条件");
            List<FieldInfo> searchFields = new List<FieldInfo>();
            searchFields.Add(this.Table.GetKeyField());
            searchFields.Add(this.Table.GetDisplayValueField());



            int fieldIndex = 0;
            foreach (FieldInfo field in searchFields)
            {
                fieldIndex++;
                CodeBuilder.AppendLine("            {0}", getSingleQueryCondition(field));
            }

            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("            SqlBuilder Sql = new SqlBuilder();");
            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("            SqlParameter[] param = ");
            CodeBuilder.AppendLine("            {");
            fieldIndex = 0;
            foreach (FieldInfo field in searchFields)
            {
                fieldIndex++;
                if (fieldIndex < searchFields.Count)
                    CodeBuilder.AppendLine("                new SqlParameter(\"@{0}\", SqlDbType.{1}){{ Value = {0} }},", field.FieldName, field.GetFieldType());
                else
                    CodeBuilder.AppendLine("                new SqlParameter(\"@{0}\", SqlDbType.{1}){{ Value = {0} }}", field.FieldName, field.GetFieldType());
            }
            CodeBuilder.AppendLine("            };");
            CodeBuilder.AppendLine("");



            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("            Sql.AppendFormat(\" SELECT \");");

            fieldIndex = 0;
            foreach (FieldInfo field in searchFields)
            {
                fieldIndex++;
                if (fieldIndex < searchFields.Count)
                    CodeBuilder.AppendLine("            Sql.AppendFormat(\"     {0}.{1}, \");", simpleTableName, field.FieldName);
                else
                    CodeBuilder.AppendLine("            Sql.AppendFormat(\"     {0}.{1}  \");", simpleTableName, field.FieldName);

            }
            CodeBuilder.AppendLine("            Sql.AppendFormat(\" FROM \");");
            CodeBuilder.AppendLine("            Sql.AppendFormat(\"     {0} AS {1} \");", Table.TableName, simpleTableName);
            CodeBuilder.AppendLine("            Sql.AppendFormat(\" WHERE \");");
            CodeBuilder.AppendLine("            Sql.AppendFormat(\"         {0}.StatusFlag = 0 \");", simpleTableName);
            CodeBuilder.AppendLine("            Sql.AppendFormat(\" AND     {0}.DisplayFlag = 1 \");", simpleTableName);


            CodeBuilder.AppendLine("            if ({0}>0)", searchFields[0].FieldName);
            CodeBuilder.AppendLine("                Sql.AppendFormat(\" AND     {1}.{0} = @{0} \");", searchFields[0].FieldName, simpleTableName);

            CodeBuilder.AppendLine("            if (!string.IsNullOrEmpty({0}) && {0}.Length>0)", searchFields[1].FieldName);
            CodeBuilder.AppendLine("                Sql.AppendFormat(\" AND     {1}.{0} = @{0} \");", searchFields[1].FieldName, simpleTableName);


            if (this.Table.IsMasterTable)
            {
                CodeBuilder.AppendLine("            Sql.AppendFormat(\" ORDER BY \");");
                CodeBuilder.AppendLine("            Sql.AppendFormat(\"     {0}.DisplayNo, {0}.{1} \");", simpleTableName, this.Table.GetKeyField().FieldName);
            }

            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("            List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();");
            CodeBuilder.AppendLine("            this.ExecuteSelectQuery(Sql.CommandText, null, out list, param);");
            CodeBuilder.AppendLine("            this.OutParam.ListParam = list;");
            CodeBuilder.AppendLine("");
            CodeBuilder.AppendLine("            return true;");

            AppendMethodEnd();
            AppandClassEnd();
            AppendNameSpaceEnd();

        }



        private string getSingleQueryConditionForSearch(FieldInfo field)
        {
            return getSingleQueryConditionForSearch("InParam.Param", field);
        }

        private string getSingleQueryConditionForSearch(string dicName, FieldInfo field)
        {
            string s = string.Empty;
            string method = GeneratorCode.Pasys.ServerControl.GetExtracterMethodName(field, true);
            string declare = field.GetNullabledDeclareTypeName();
            if (method.Length > 0)
            {
                s = string.Format("{0} {1} = ClientValueExtracter.{2}({3}, \"{1}\", false);",
                    declare, field.FieldName, method, dicName);
            }
            else
            {
                s = string.Format("{0} {1} =({0}){2}[ \"{1}\"];",
                    field.FieldType.MapName, field.FieldName, dicName);
            }
            return s;

        }


        private string getSingleQueryCondition(FieldInfo field)
        {
            return getSingleQueryCondition("InParam.Param", field);
        }

        /// <summary>
        /// declare variable and get value from InParam.Param
        /// </summary>
        /// <param name="field"></param>
        /// <returns></returns>
        private string getSingleQueryCondition(string dicName, FieldInfo field)
        {
            string s = string.Empty;
            string method = GeneratorCode.Pasys.ServerControl.GetExtracterMethodName(field);
            if (method.Length > 0)
            {
                s = string.Format("{0} {1} = ClientValueExtracter.{2}({3}, \"{1}\", false);",
                    field.FieldType.MapName, field.FieldName, method, dicName);
            }
            else
            {
                s = string.Format("{0} {1} =({0}){2}[ \"{1}\"];",
                    field.FieldType.MapName, field.FieldName, dicName);
            }
            return s;

        }

        //protected override void appandClassStart(string opeatorType, string functionDesc)
        //{
        //    codeBuilder.AppendLine("    /// <summary>");
        //    codeBuilder.AppendLine("    {0}", functionDesc); // 
        //    codeBuilder.AppendLine("    /// </summary>");
        //    codeBuilder.AppendLine("    public class {0}_{1} : ServerControlBase", this.Table.EntityName, opeatorType);
        //    codeBuilder.AppendLine("    {");

        //}


        protected override void AppendMethodStart()
        {
            CodeBuilder.AppendLine("        /// <summary>");
            CodeBuilder.AppendLine("        /// サーバーコントロール実行");
            CodeBuilder.AppendLine("        /// </summary>");
            CodeBuilder.AppendLine("        /// <returns>true：正常、false：異常</returns>");
            CodeBuilder.AppendLine("        [UseAutoConnection]");
            CodeBuilder.AppendLine("        public bool ServerControlExecute()");
            CodeBuilder.AppendLine("        {");
        }



    }
}
